package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MInvoice;
import org.compiere.model.MInvoiceLine;
import org.compiere.model.MOrg;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.MHREmployee;
import org.utils.fwMoney;

import extend.org.compiere.utils.Util;

/**
 * @author A.Kulantayev
 *
 */
public class FI_Form_T3 extends SvrProcess {
	//FI_Form_T3_
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	/**Current Record_ID */
	private int p_Record_ID = 0;	
	/**Out file */
	String ResultPath;
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		p_Record_ID = pi.getRecord_ID();
		//remove after test
		//p_Record_ID = 1000016;
		//
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("C_Invoice_ID")) {
				p_Record_ID = ((BigDecimal) para[i].getParameter()).intValue();
			}
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
		if(p_Record_ID==0){
			log.log(Level.SEVERE, "Error initializing the parameter Record_ID: p_Record_ID ="+p_Record_ID);
			throw new AdempiereException(Msg.translate(m_ctx,"Error initializing the parameter Record_ID. Parameter does not exists"));
		}

	}

	@Override
	protected String doIt() throws Exception {
		MInvoice advanStatement = new MInvoice(m_ctx, p_Record_ID, get_TrxName());
		//File path
		  ResultPath = Util.getResultPath("АВАНСОВЫЙ_ОТЧЕТ_"+advanStatement.getDocumentNo()
				  , new String("АВАНСОВЫЙ_ОТЧЕТ_"+advanStatement.getDocumentNo()).length()+1);
		  
		  MAttachmentEntry entry = Util. getAttachment(pi, m_ctx, ResultPath);
		  
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		XSSFWorkbook wb = new XSSFWorkbook(entry.getInputStream());
		XSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		R = sheet.getRow(7);
		MOrg org = new MOrg(m_ctx, advanStatement.getAD_Org_ID(), get_TrxName());
		MBPartner ad_Org_BP = new MBPartner(m_ctx, org.getLinkedC_BPartner_ID(get_TrxName()), get_TrxName());
		R.getCell(21).setCellValue(org.getName());
		R.getCell(57).setCellValue(ad_Org_BP.getFI_BIN_IIN());

		
		//Sub Header Block
		R = sheet.getRow(17);
		R.getCell(0).setCellValue(advanStatement.getDocumentNo());
		R.getCell(16).setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(advanStatement.getDateInvoiced()));
		R.getCell(32).setCellValue(" ");
		R.getCell(48).setCellValue(" ");
		//About Employee Info
		sheet.getRow(21).getCell(17).setCellValue(advanStatement.getC_BPartner().getLastName()+" "
													+advanStatement.getC_BPartner().getFirstName().substring(0, 1)+"."
													+advanStatement.getC_BPartner().getMiddleName().substring(0, 1)+".");
		
		String sql = "SELECT HR_Employee_ID FROM HR_Employee WHERE IsActive = 'Y' AND C_BPartner_ID = "+advanStatement.getC_BPartner_ID();
		int employee_ID = 0;
		employee_ID = DB.getSQLValue(get_TrxName(), sql);
		if(employee_ID==0)
			throw new AdempiereException("Подотчетное лицо не является сотрудником");
		MHREmployee employee = new MHREmployee(m_ctx, employee_ID, get_TrxName());
		R = sheet.getRow(22);
		R.getCell(10).setCellValue(employee.getHR_Job().getName()==null?"--":employee.getHR_Job().getName());
		R.getCell(42).setCellValue(String.valueOf(advanStatement.getC_BPartner().gethrm_tableNumber()));
		sheet.getRow(23).getCell(10).setCellValue(advanStatement.getDescription()==null?"--":advanStatement.getDescription());
		//preview advance
		sql = "SELECT SUM(AmtAcctDr - AmtAcctCr) FROM Fact_Acct fa"+ 
				"\nWHERE fa.AD_Client_ID = "+advanStatement.getAD_Client_ID()+ 
				"\nAND fa.AD_Org_ID = "+advanStatement.getAD_Org_ID()+
				"\nAND fa.C_AcctSchema_ID = 1000000"+ 
				"\nAND fa.IsActive='Y'"+
				"\nAND fa.PostingType='A'"+
				"\nAND fa.Account_ID = ?"+ 
				"\nAND fa.C_BPartner_ID = "+advanStatement.getC_BPartner_ID()+
				"\nAND fa.DateAcct <="+DB.TO_DATE(advanStatement.getDateAcct(), false)+
				"\nAND NOT (fa.AD_Table_ID = 318 AND fa.Record_ID = "+advanStatement.getC_Invoice_ID()+")";
		BigDecimal prevAdvSum = DB.getSQLValueBD(get_TrxName(), sql, 1005176);
		sheet.getRow(27).getCell(24).setCellValue(prevAdvSum.doubleValue());
		
		BigDecimal disbursed = advanStatement.getGrandTotal();
		//sheet.getRow(32).getCell(24).setCellValue(disbursed.doubleValue());
		sheet.getRow(33).getCell(24).setCellValue(disbursed.doubleValue());
		
		if(prevAdvSum.compareTo(disbursed)==1)
			sheet.getRow(34).getCell(24).setCellValue((prevAdvSum.add(disbursed.negate())).doubleValue());
		if(prevAdvSum.compareTo(disbursed)==-1)
			sheet.getRow(35).getCell(24).setCellValue(disbursed.add(prevAdvSum.negate()).doubleValue());
		//
		//Accounting fact_accts
		factAdvSt[] advanStatementFacts = getFacts(advanStatement.getAD_Client_ID(), advanStatement.getAD_Org_ID(), p_Record_ID);
		for(int i = 0, strRow = 27; i<advanStatementFacts.length; i++, strRow++){
			R = sheet.getRow(strRow);
			R.getCell(33).setCellValue(advanStatementFacts[i].getDrValue());
			R.getCell(45).setCellValue(advanStatementFacts[i].getCrValue());
			R.getCell(57).setCellValue(advanStatementFacts[i].getAmtAcct().doubleValue());
		}
		//Approved sum
		sheet.getRow(38).getCell(14).setCellValue(new fwMoney(advanStatement.getGrandTotal().doubleValue()).num2str().substring(0, 1).toUpperCase()
				+new fwMoney(advanStatement.getGrandTotal().doubleValue()).num2str().substring(1, new fwMoney(advanStatement.getGrandTotal().doubleValue()).num2str().length()));
		//
		//
		sheet.getRow(42).getCell(18).setCellValue(new fwMoney(prevAdvSum.add(disbursed.negate()).abs().doubleValue()).num2str().substring(0, 1).toUpperCase()
				+new fwMoney(prevAdvSum.add(disbursed.negate()).abs().doubleValue()).num2str().substring(1, new fwMoney(prevAdvSum.add(disbursed.negate()).abs().doubleValue()).num2str().length()));
		//
		//
		R = sheet.getRow(49);
		R.getCell(14).setCellValue(advanStatement.getC_BPartner().getLastName()+" "
													+advanStatement.getC_BPartner().getFirstName().substring(0,1)+"."
													+advanStatement.getC_BPartner().getMiddleName().substring(0, 1)+".");
		R.getCell(43).setCellValue(advanStatement.getDocumentNo());
		R.getCell(49).setCellValue(new SimpleDateFormat("dd").format(advanStatement.getDateInvoiced()));
		
		sheet.getRow(51).getCell(6).setCellValue(new fwMoney(advanStatement.getGrandTotal().doubleValue()).num2str().substring(0, 1).toUpperCase()
				+new fwMoney(advanStatement.getGrandTotal().doubleValue()).num2str().substring(1, new fwMoney(advanStatement.getGrandTotal().doubleValue()).num2str().length()));
		
		R.getCell(52).setCellValue(new SimpleDateFormat("MMMMM").format(advanStatement.getDateInvoiced()));
		R.getCell(57).setCellValue(new SimpleDateFormat("yyyy").format(advanStatement.getDateInvoiced()));
		//
		
		//AdvanceStatement lines
		int rowNo = 60;
		MInvoiceLine[] advstatLines = advanStatement.getLines();

		for(int i = 0, no=1; i< advstatLines.length; i++, no++,rowNo++){
			sheet.shiftRows(rowNo, rowNo+4, 1);
			R = sheet.createRow(rowNo);
			for(int j = 0; j<64; j++){
				R.createCell(j).setCellStyle(sheet.getRow(59).getCell(55).getCellStyle());
			}
			//col1
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 0, 3));
			R.getCell(0).setCellValue(String.valueOf(no));
			R.getCell(0).setCellStyle(sheet.getRow(59).getCell(0).getCellStyle());
			//col2
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 4, 9));
			R.getCell(4).setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(advstatLines[i].getC_Invoice().getDateInvoiced()));
			//col3
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 10, 16));
			R.getCell(10).setCellValue(advstatLines[i].getC_Invoice().getDocumentNo());
			//col4
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 17, 25));
			R.getCell(17).setCellValue(advstatLines[i].getDescription());
			//col5
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 26, 47));
			String why="";
			if(advstatLines[i].getC_BPartner_ID()!=0)
				why+=advstatLines[i].getC_BPartner().getName();
			if(advstatLines[i].getC_Charge_ID()!=0)
				why+=" "+advstatLines[i].getC_Charge().getName();
			R.getCell(26).setCellValue(why);
			//col6
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 48, 55));
			R.getCell(48).setCellStyle(sheet.getRow(27).getCell(57).getCellStyle());
			R.getCell(48).setCellValue(advstatLines[i].getPriceEntered().doubleValue());
			//col7
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 56, 63));
			for(int k = 0; k< advanStatementFacts.length; k++){
				if(advanStatementFacts[k].getLine_ID()==advstatLines[i].getC_InvoiceLine_ID())
					R.getCell(56).setCellValue(advanStatementFacts[k].getDrValue());
			}
		}
//		sheet.getRow(rowNo).getCell(48).setCellValue(advanStatement.getGrandTotal().doubleValue());
		sheet.getRow(rowNo).getCell(48).setCellFormula("SUM(AW61:AW" + rowNo + ")");
		
		sheet.getRow(rowNo+2).getCell(15).setCellValue(advanStatement.getC_BPartner().getName());
		//
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
		Util.launchFile(new File(ResultPath));	
		return Msg.translate(m_ctx, "Success");
	}
	
	public factAdvSt[] getFacts(int AD_Client_ID, int AD_Org_ID,int Record_ID){
		factAdvSt factA = null;
		List<factAdvSt> list = new ArrayList<factAdvSt>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT fa1.line_id,c_dr.value AS dr_value,c_cr.value AS cr_value"+
					 "\n ,CASE WHEN abs(fa2.amtacctcr) > abs(fa1.amtacctdr)"+
					 "\n THEN fa1.amtacctdr ELSE fa2.amtacctcr"+
					 "\n END AS amtacct"+
					 "\n FROM fact_acct fa1"+
					 "\n INNER JOIN fact_acct fa2"+
					  	"\n ON fa1.ad_table_id = fa2.ad_table_id"+
					  	"\n AND fa1.record_id = fa2.record_id"+
					  	"\n AND fa1.ad_org_id = fa2.ad_org_id"+
					  	"\n AND fa2.amtacctcr != 0"+
					  	"\n AND fa1.amtacctdr != 0"+
					  	"\n AND fa1.account_id != fa2.account_id"+
					  	"\n AND fa1.counter = fa2.reference"+
					 "\n LEFT JOIN c_elementvalue c_dr ON(fa1.account_id=c_dr.c_elementvalue_id)"+
					 "\n LEFT JOIN c_elementvalue c_cr ON(fa2.account_id=c_cr.c_elementvalue_id)"+
					 "\n WHERE fa1.IsActive='Y'"+ 
					 "\n AND fa1.PostingType='A'"+
					 "\n AND fa1.C_Currency_ID = 341"+
					 "\n AND fa1.AD_Table_ID = 318"+ 
					 "\n AND fa1.AD_Client_ID = "+ AD_Client_ID+
					 "\n AND fa1.AD_Org_ID = "+AD_Org_ID+
					 "\n AND fa1.C_AcctSchema_ID = 1000000"+ 
					 "\n AND fa1.Record_ID = "+Record_ID;
		try{
			pstmt = DB.prepareStatement(sql, get_TrxName());
			rs = pstmt.executeQuery();
			while(rs.next()){
				factA = new factAdvSt(rs.getInt("line_id"),rs.getString("dr_value"), rs.getString("cr_value"), rs.getBigDecimal("amtacct"));
				list.add(factA);
				factA = null;
			}
			
		}catch(Exception e){
			log.log(Level.SEVERE, sql, e);
		}finally{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		return list.toArray(new factAdvSt[list.size()]);
	}
	private class factAdvSt{
		
		public factAdvSt(int line_ID, String drValue, String crValue,
				BigDecimal amtAcct) {
			super();
			this.line_ID = line_ID;
			this.drValue = drValue;
			this.crValue = crValue;
			this.amtAcct = amtAcct;
		}
		private int line_ID = 0;
		private String drValue;
		private String crValue;
		private BigDecimal amtAcct;
		
		public int getLine_ID() {
			return line_ID;
		}
		public void setLine_ID(int line_ID) {
			this.line_ID = line_ID;
		}
		public String getDrValue() {
			return drValue;
		}
		public void setDrValue(String drValue) {
			this.drValue = drValue;
		}
		public String getCrValue() {
			return crValue;
		}
		public void setCrValue(String crValue) {
			this.crValue = crValue;
		}
		public BigDecimal getAmtAcct() {
			return amtAcct;
		}
		public void setAmtAcct(BigDecimal amtAcct) {
			this.amtAcct = amtAcct;
		}
	}

}
